﻿using DotNetCommon;
using DotNetCommon.Extensions;
using System.Threading;
using System.Threading.Tasks;

namespace DBUtil;

public partial class DBAccess
{
    /// <summary>
    /// 数据库版本的兼容性, 生成sql的时候会有帮助
    /// </summary>
    public EnumDBVersionCompatibility? DBVersionCompatibility => Setting?.DBVersionCompatibility;

    #region DBVersion & DBName & SchemaName & UserName & LoginUserName
    private string dbVersion = null;
    /// <summary>
    /// 数据库版本
    /// </summary>        
    public virtual string DBVersion => RefreshUserAndDbInfoAsync().Result.dbVersion;

    private string dbName = null;
    /// <summary>
    /// 当前连接的数据库名称
    /// </summary>
    public virtual string DBName => RefreshUserAndDbInfoAsync().Result.dbName;

    private string schemaName = null;
    /// <summary>
    /// 当前连接默认的Schema名称
    /// </summary>
    public virtual string SchemaName => RefreshUserAndDbInfoAsync().Result.schemaName;

    private string userName = null;
    /// <summary>
    /// 操作当前数据库的用户名(如果是在sqlserver下, 这个值可能是 dbo 而不是 sa)
    /// </summary>
    public virtual string UserName => RefreshUserAndDbInfoAsync().Result.userName;

    private string loginUserName = null;
    /// <summary>
    /// 登录数据库使用的用户名(如果是在sqlserver下, 这个值可能是 sa 而不是 dbo)
    /// </summary>
    public virtual string LoginUserName => RefreshUserAndDbInfoAsync().Result.loginUserName;

    /// <summary>
    /// 数据库版本
    /// </summary>        
    public virtual Task<string> DBVersionAsync => RefreshUserAndDbInfoAsync().ContinueWith(i => i.Result.dbVersion);

    /// <summary>
    /// 当前连接的数据库名称
    /// </summary>
    public virtual Task<string> DBNameAsync => RefreshUserAndDbInfoAsync().ContinueWith(i => i.Result.dbName);

    /// <summary>
    /// 当前连接默认的Schema名称
    /// </summary>
    public virtual Task<string> SchemaNameAsync => RefreshUserAndDbInfoAsync().ContinueWith(i => i.Result.schemaName);

    /// <summary>
    /// 操作当前数据库的用户名(如果是在sqlserver下, 这个值可能是 dbo 而不是 sa)
    /// </summary>
    public virtual Task<string> UserNameAsync => RefreshUserAndDbInfoAsync().ContinueWith(i => i.Result.userName);

    /// <summary>
    /// 登录数据库使用的用户名(如果是在sqlserver下, 这个值可能是 sa 而不是 dbo)
    /// </summary>
    public virtual Task<string> LoginUserNameAsync => RefreshUserAndDbInfoAsync().ContinueWith(i => i.Result.loginUserName);

    /// <summary>
    /// 刷新db链接信息: DBName/DBVersion/UserName/LoginUserName
    /// </summary>
    /// <param name="force">是否强制刷新, 默认 false, 即: 如果已经缓存过则不再执行</param>
    public async Task<(string loginUserName, string userName, string dbName, string dbVersion, string schemaName)> RefreshUserAndDbInfoAsync(bool force = false)
    {
        if (!force
            && loginUserName.IsNotNullOrWhiteSpace()
            && userName.IsNotNullOrWhiteSpace()
            && dbName.IsNotNullOrWhiteSpace()
            && dbVersion.IsNotNullOrWhiteSpace()
            && schemaName.IsNotNullOrWhiteSpace()
            ) return (loginUserName, userName, dbName, dbVersion, schemaName);
        var sql = $"select {GetCurrentLoginUserSqlSeg()},{GetCurrentUserSqlSeg()},{GetCurrentDataBaseSqlSeg()},{GetCurrentDataBaseVersionSqlSeg()},{GetCurrentSchemaSqlSeg()}";
        await SelectDataReaderAsync(async reader =>
        {
            await reader.RawReader.ReadAsync();
            loginUserName = reader.RawReader.GetString(0);
            userName = reader.RawReader.GetString(1);
            dbName = reader.RawReader.GetString(2);
            dbVersion = reader.RawReader.GetString(3);
            schemaName = reader.RawReader.GetString(4);
        }, sql);
        return (loginUserName, userName, dbName, dbVersion, schemaName);
    }
    #endregion

    #region 获取当前db/schema/user的sqlseg
    /// <summary>
    /// 获取当前schema名的sql,如:
    /// <list type="bullet">
    /// <item>MySql: "database()", 同: <seealso cref="GetCurrentDataBaseSqlSeg"/></item>
    /// <item>SqlServer: SCHEMA_NAME()</item>
    /// </list>
    /// </summary>
    /// <returns></returns>
    public abstract string GetCurrentSchemaSqlSeg();

    /// <summary>
    /// 获取当前数据库名的sql,如:
    /// <list type="bullet">
    /// <item>MySql: "database()",同: <seealso cref="GetCurrentSchemaSqlSeg"/></item>
    /// <item>SqlServer: "DB_NAME()"</item>
    /// </list>
    /// </summary>
    /// <returns></returns>
    public abstract string GetCurrentDataBaseSqlSeg();

    /// <summary>
    /// 获取当前用户名的sql,如:
    /// <list type="bullet">
    /// <item>MySql: "user()", 注意: user()返回的格式为: username@ip,如果想返回纯净用户名,传参isPure=true. 效果同: <seealso cref="GetCurrentLoginUserSqlSeg"/></item>
    /// <item>SqlServer: "CURRENT_USER", 注意: 此处返回的是 dbo, 如果想返回 sa,需要使用 <seealso cref="GetCurrentLoginUserSqlSeg"/></item>
    /// </list>
    /// </summary>
    /// <remarks>注意: 因为Mysql的 select user() 返回的用户名如: <c>root@localhost</c> ,所以增加参数 <c>isPure</c> ,默认为false, 当设为true时,则返回不带ip后缀的用户名,如: <c>root</c> </remarks>
    public abstract string GetCurrentUserSqlSeg(bool isPure = false);

    /// <summary>
    /// 获取当前用户名的sql,如:
    /// <list type="bullet">
    /// <item>MySql: "user()", 注意: user()返回的格式为: username@ip,如果想返回纯净用户名,传参isPure=true. 效果同: <seealso cref="GetCurrentUserSqlSeg"/></item>
    /// <item>SqlServer: "SYSTEM_USER", 注意: 此处返回的是 sa, 如果想返回 dbo,需要使用 <seealso cref="GetCurrentUserSqlSeg"/></item>
    /// </list>
    /// </summary>
    /// <remarks>注意: 因为Mysql的 select user() 返回的用户名如: <c>root@localhost</c> ,所以增加参数 <c>isPure</c> ,默认为false, 当设为true时,则返回不带ip后缀的用户名,如: <c>root</c> </remarks>
    public abstract string GetCurrentLoginUserSqlSeg(bool isPure = false);

    /// <summary>
    /// 获取当前数据库实例版本的sql,如:
    /// <list type="bullet">
    /// <item>MySql: "version()"</item>
    /// <item>SqlServer: "@@VERSION"</item>
    /// </list>
    /// </summary>
    /// <returns></returns>
    public abstract string GetCurrentDataBaseVersionSqlSeg();
    #endregion

    #region Is系列: 判断 表/视图/列/存储过程/触发器 是否存在
    /// <summary>
    /// 列是否存在的sql
    /// </summary>
    protected abstract string IsColumnExistInTableOrViewSql(string tableOrViewPureName, string columnPureName, string schemaPureName = null);
    /// <summary>
    /// 判断指定表或视图中是否有某一列
    /// </summary>
    public virtual async Task<bool> IsColumnExistInTableOrViewAsync(string tableOrViewPureName, string columnPureName, string schemaPureName = null, CancellationToken cancellationToken = default)
    {
        var sql = IsColumnExistInTableOrViewSql(tableOrViewPureName, columnPureName, schemaPureName);
        var r = await SelectScalarAsync<int>(sql, cancellationToken);
        return r > 0;
    }
    /// <summary>
    /// 表或视图是否存在的sql
    /// </summary>
    protected abstract string IsTableOrViewExistSql(string tableOrViewPureName, string schemaPureName = null);

    /// <summary>
    /// 判断表或视图是否存在
    /// </summary>
    public virtual async Task<bool> IsTableOrViewExistAsync(string tableOrViewPureName, string schemaPureName = null, CancellationToken cancellationToken = default)
    {
        AssertUtil.NotNullOrWhiteSpace(tableOrViewPureName);
        var sql = IsTableOrViewExistSql(tableOrViewPureName, schemaPureName);
        var r = await SelectScalarAsync<int>(sql, cancellationToken);
        return r > 0;
    }

    /// <summary>
    /// 判断存储过程是否存在
    /// </summary>
    public abstract Task<bool> IsProcedureExistAsync(string procPureName, string schemaPureName = null, CancellationToken cancellationToken = default);

    /// <summary>
    /// 判断触发器是否存在
    /// </summary>
    public abstract Task<bool> IsTriggerExistAsync(string triggerPureName, string schemaPureName = null, CancellationToken cancellationToken = default);
    #endregion
}
